import streamlit as st
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import gurobipy as gp
from gurobipy import GRB
import datetime
from datetime import timedelta
import io
import os
import re
from sqlalchemy.engine import create_engine
import subprocess
import sys
from pathlib import Path
from loguru import logger
import time
import concurrent.futures
from threading import Lock
import threading
import queue
from concurrent.futures import ThreadPoolExecutor, as_completed
import uuid

# Import utility functions from existing files
from data_utils_system import calculate_ahs_couponed_price, calculate_zz_couponed_price, calculate_strategy_value, calculate_strategy_quote_price
from data_utils_system import level_order, level_hierarchy, calculate_inversion_statistics, calculate_big_gap_value, calculate_big_gap_value_human, define_competiitive_starts_ends


# Set page configuration
st.set_page_config(
    page_title="多品类价格策略分析平台",
    page_icon="📊",
    layout="wide",
    initial_sidebar_state="expanded"
)

# Custom CSS
st.markdown("""
<style>
    .main-header {
        font-size: 2.5rem;
        color: #FF5733;
        text-align: center;
        margin-bottom: 1rem;
    }
    .sub-header {
        font-size: 1.5rem;
        color: #4B8BBE;
        margin-bottom: 1rem;
    }
    .card {
        padding: 1rem;
        border-radius: 5px;
        box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
        margin-bottom: 1rem;
        background-color: #F8F9FA;
    }
    .metric-card {
        background-color: #EAF2F8;
        text-align: center;
        padding: 1rem;
        border-radius: 5px;
    }
    .metric-value {
        font-size: 2rem;
        font-weight: bold;
        color: #1E90FF;
    }
    .metric-label {
        font-size: 1rem;
        color: #666;
    }
</style>
""", unsafe_allow_html=True)

# Initialize database connection
@st.cache_resource
def get_db_connection():
    try:
        engine = create_engine('presto://u_algorithm@trino.aihuishou.com:9090/hive/default')
        return engine
    except Exception as e:
        st.error(f"数据库连接错误: {e}")
        return None

# Load product data
@st.cache_data
def load_product_names():
    return [
        '苹果 iPhone 11', '苹果 iPhone 12', '苹果 iPhone 13', '苹果 iPhone 14', '苹果 iPhone 15', '苹果 iPhone 16',
        '苹果 iPhone 11 Pro', '苹果 iPhone 12 Pro', '苹果 iPhone 13 Pro', '苹果 iPhone 14 Pro', '苹果 iPhone 15 Pro', '苹果 iPhone 16 Pro',
        '苹果 iPhone 11 Pro Max', '苹果 iPhone 12 Pro Max', '苹果 iPhone 13 Pro Max', '苹果 iPhone 14 Pro Max', '苹果 iPhone 15 Pro Max', '苹果 iPhone 16 Pro Max',
    ]

# Load results data from Excel files
@st.cache_data
def load_results_data(file_path):
    try:
        if os.path.exists(file_path):
            return pd.read_excel(file_path)
        else:
            return None
    except Exception as e:
        st.error(f"加载文件错误: {e}")
        return None

@st.cache_data
def get_base_data_for_date(target_date):
    """获取指定日期的全量基础数据"""
    try:
        conn = get_db_connection()
        date_str = target_date.strftime('%Y-%m-%d')

        # 检查时间是否满足要求（当天需要15:10之后）
        current_datetime = datetime.datetime.now()
        current_date = current_datetime.date()

        if target_date == current_date and current_datetime.hour < 15 or (current_datetime.hour == 15 and current_datetime.minute < 10):
            return {
                "success": False,
                "message": "当天数据需要等到15:10之后才能获取",
                "data": None
            }
        
        # 查询该日期的全量数据
        query = f"""
        select 
            * 
        from  
            algo.system_new_g1_today_data
        where 
            partition_date = '{date_str}'
            and product_category_id != 3  -- 2025-05-14 除笔记本之外
        """
        
        base_data = pd.read_sql(query, conn)

        if base_data.empty:
            return {
                "success": False,
                "message": f"未找到 {date_str} 日期的数据",
                "data": None
            }
        base_data = base_data[base_data['finalprice'] != '(NULL)']
        base_data = base_data[base_data['product_level_name'] != 'S']

        base_data['human_price'] = base_data['finalprice']

        # 计算AHS券后价
        base_data['ahs_couponed_price'] = base_data['finalprice'].apply(calculate_ahs_couponed_price)
        # 计算转转券后价
        base_data['zz_couponed_price_18_added'] = base_data['zz_pre_coupon_price'].apply(calculate_zz_couponed_price)

        # 30天成交量
        base_data['success_nums'] = base_data['success_nums'].fillna(0)
        base_data['success_nums'] = base_data['success_nums'].astype(int)
        # 当日成交量
        base_data['curr_success_nums'] = base_data['curr_success_nums'].fillna(0)
        base_data['curr_success_nums'] = base_data['curr_success_nums'].astype(int)
        # 30天询价量
        base_data['inquiry_nums'] = base_data['inquiry_nums'].fillna(0)
        base_data['inquiry_nums'] = base_data['inquiry_nums'].astype(int)

        base_data['参照项'] = base_data['product_sku_id'].astype(str) + "_" + base_data['product_level_name'].astype(str)
        base_data['券后价差-ahs-转转'] = base_data['ahs_couponed_price'] - base_data['zz_couponed_price_18_added']
        base_data['转转追价-策略-中间值'] = base_data['zz_couponed_price_18_added'].apply(calculate_strategy_value)
        base_data['转转追价-策略报价'] = base_data.apply(calculate_strategy_quote_price, axis=1)
        base_data['转转追价-策略报价-券后'] = base_data['转转追价-策略报价'].apply(calculate_ahs_couponed_price)

        base_data['zz_strategy_price'] = base_data['转转追价-策略报价']
        base_data['zz_strategy_price_couponed'] = base_data['转转追价-策略报价-券后']
        base_data['standard_price'] = base_data['standard_price'].fillna(base_data['finalprice'])
        base_data['standard_price'] = base_data['standard_price'].replace('(NULL)', 0)
        base_data['standard_price'] = base_data['standard_price'].astype(int)

        base_data['sku_level'] = base_data['product_sku_id'].astype(str) + '_' + base_data['product_level_name']
        base_data['sku_A+'] = base_data['product_sku_id'].astype(str) + '_' + 'A+'

        return base_data
    except Exception as e:
        st.error(f"获取基础数据时出错: {str(e)}")
        return None

def process_single_product(combo, base_data, lock):
    """处理单个产品的函数，用于多线程并行处理"""
    product_name = combo['product']
    target_date = combo['date']
    target = combo['competitiveness']
    
    # 记录开始时间
    start_time = time.time()
    
    try:
        # 如果有基础数据，从中筛选该产品的数据
        if base_data is not None:
            result = run_specific_product_bi_price(base_data, product_name, target_date, target)
        else:
            # 如果没有基础数据，使用原有逻辑
            return {
                "product": product_name,
                "date": target_date,
                "competitiveness": target,
                "success": False,
                "error": "基础数据为空",
                "message": f"{product_name} - 执行失败: 基础数据为空",
                "process_time": 0
            }

        # 计算处理时间
        process_time = time.time() - start_time
        
        # 处理执行结果
        if result['success']:
            # 构造成功信息
            result_info = {
                "product": product_name,
                "date": target_date,
                "competitiveness": target,
                "success": True,
                "process_time": process_time
            }
            
            with lock:
                # 使用锁保护对全局变量的访问
                message = f"{product_name} - 执行成功 (耗时: {process_time:.2f}秒)"
                # 将执行成功的消息添加到返回结果中
                result_info['message'] = message
                result_info['data'] = result.get('data')
                result_info['sku_level_df'] = result.get('sku_level_df')
                result_info['selected_chosen_results'] = result.get('selected_chosen_results')
            
            return result_info
        else:
            # 构造失败信息
            error_message = result.get('message', '未知错误')
            result_info = {
                "product": product_name,
                "date": target_date,
                "competitiveness": target,
                "success": False,
                "error": error_message,
                "process_time": process_time
            }
            
            with lock:
                message = f"{product_name} - 执行失败: {error_message} (耗时: {process_time:.2f}秒)"
                result_info['message'] = message
            
            return result_info

    except Exception as e:
        # 计算处理时间
        process_time = time.time() - start_time
        
        # 处理执行过程中的异常
        error_message = str(e)
        result_info = {
            "product": product_name,
            "date": target_date,
            "competitiveness": target,
            "success": False,
            "error": error_message,
            "process_time": process_time
        }
        
        with lock:
            message = f"{product_name} - 执行出错: {error_message} (耗时: {process_time:.2f}秒)"
            result_info['message'] = message
        
        return result_info


# BI价格策略(型号)功能实现
def run_specific_product_bi_price(base_data, product_name, target_date, target):
    """
    根据指定产品型号、日期和预期竞争力运行BI价格策略
    
    参数:
    product_name (str): 产品型号名称
    target_date (datetime.date): 目标日期
    expected_competitiveness (float): 预期竞争力, 范围0-1
    
    返回:
    dict: 包含处理结果的字典
    """
    try:
        # 创建必要目录
        os.makedirs('collections', exist_ok=True)
        os.makedirs('sku_level', exist_ok=True)

        try:

            # 根据预期竞争力调整价格
            logger.info("--------当前型号为：{}, 当前目标竞争力为：{}--------------".format(product_name, target))

            candidates = []  # 2025-02-18 每个竞争力一个候选集
            df_container = {}
            results = []

            chosen_df = pd.DataFrame()  # 每天的"汇总"结果
            chosen_detailed_df = pd.DataFrame()  # 每天的"明细"结果
            sku_level_df = pd.DataFrame()  # 每天的"sku-level"结果

            MAX_PRICE_GAP = 0.15
            MAX_ANTI_PRICE = 0
            LIMIT_ANTI_RATE = 0.05
            
            df = base_data[base_data['product_name'] == product_name].copy()

            product_brand_name = df['product_brand_name'].tolist()[0]

            # 每次循环创建 df 和 unique_combinations 的副本
            df['cost'] = df['standard_price'] * df['success_nums']
            df['cost_daily'] = df['standard_price'] * df['curr_success_nums']
            estimated_total_cost = sum(df['cost'])
            inquiry_nums_30d_total = sum(df['inquiry_nums'])
            success_nums_30d_total = sum(df['success_nums'])
            real_total_cost_daily = sum(df['cost_daily'])
            success_nums_daily_total = sum(df['curr_success_nums']) 
            
            df['target'] = target  # 2025-02-18 新增，用于区分同一个产品型号，不同竞争力的结果
            unique_combinations = df[['product_sku_name', 'product_level_name']].drop_duplicates().reset_index(drop=True)

            # 遍历每个 SKU+级别，生成候选的价格调整方案
            combination_container = []
            grouped = df.groupby(['product_sku_name', 'product_level_name'])

            for (sku, level), group in grouped:
                ahs_ori_price = group['finalprice'].iloc[0]
                ahs_couponed_price = group['ahs_couponed_price'].iloc[0]
                zz_strategy_prices = group['zz_strategy_price'].unique()

                # 增加限定条件价差<=15%：调价的幅度不能超过ahs_bi_std_price的15% 即(adjusted_prices - ahs_ori_price) / ahs_bi_std_price <= 0.15
                ahs_bi_std_price = group['standard_price'].iloc[0]
                # Determine the range of price adjustments
                if ahs_ori_price >= zz_strategy_prices.max():
                    # Case 1: ahs_ori_price >= all zz_strategy_price
                    adjusted_prices = [ahs_ori_price]
                elif ahs_ori_price <= zz_strategy_prices.min():
                    # Case 2: ahs_ori_price <= all zz_strategy_price
                    adjusted_prices = [ahs_ori_price] + [zz_strategy_price for zz_strategy_price in zz_strategy_prices if (zz_strategy_price - ahs_ori_price) / ahs_bi_std_price <= MAX_PRICE_GAP]
                else:
                    # Case 3: ahs_ori_price is between some zz_strategy_prices
                    adjusted_prices = [ahs_ori_price] + [zz_strategy_price for zz_strategy_price in zz_strategy_prices if
                                                         (zz_strategy_price - ahs_ori_price) / ahs_bi_std_price <= MAX_PRICE_GAP and zz_strategy_price > ahs_ori_price]

                # Calculate payed_cost and compete_gained for each adjusted price
                for adjusted_price in adjusted_prices:
                    adjusted_couponed_price = calculate_ahs_couponed_price(adjusted_price)
                    conditioned_b2b_count = group['success_nums'].sum()
                    compete_gained = group[group['zz_couponed_price_18_added'] <= adjusted_couponed_price]['inquiry_nums'].sum()
                    condittioned_price_gap = adjusted_couponed_price - ahs_couponed_price
                    payed_cost = conditioned_b2b_count * condittioned_price_gap

                    # Find the corresponding zz_strategy_price and zz_couponed_price_18_added
                    if adjusted_price in group['zz_strategy_price'].values:
                        zz_strategy_price = adjusted_price
                        zz_couponed_price_18_added = group[group['zz_strategy_price'] == adjusted_price]['zz_couponed_price_18_added'].iloc[0]
                    else:
                        zz_strategy_price = ahs_ori_price
                        zz_couponed_price_18_added = ahs_couponed_price

                    # Append the result
                    combination_container.append({
                        'product_sku_name': sku,
                        'product_level_name': level,
                        'key': f"{sku}_{level}_{adjusted_price}",
                        'finalprice': ahs_ori_price,
                        'adjusted_price': adjusted_price,
                        'adjusted_couponed_price': adjusted_couponed_price,
                        'zz_strategy_price': zz_strategy_price,
                        'zz_couponed_price_18_added': zz_couponed_price_18_added,
                        'ahs_couponed_price': ahs_couponed_price,
                        'conditioned_b2b_count': conditioned_b2b_count,
                        'condittioned_price_gap': condittioned_price_gap,
                        'payed_cost': payed_cost,
                        'compete_gained': compete_gained
                    })

            # Create a DataFrame from the results
            combination_df = pd.DataFrame(combination_container)

            # Sort the result DataFrame by ahs_sku_name and level_order
            combination_df['level_sort'] = combination_df['product_level_name'].apply(lambda x: level_order.index(x) if x in level_order else len(level_order))
            combination_df = combination_df.sort_values(by=['product_sku_name', 'level_sort']).drop(columns=['level_sort'])

            # 2025-02-07 用新的求解器 Gurobi 构建混合整数规划模型求解
            model = gp.Model("Minimize_Cost_Maximize_Competitiveness")

            # 如果不想在控制台输出求解信息，可设置如下参数：
            model.Params.OutputFlag = 0
            # 设置求解器参数：放宽时间限制和最优性差距
            model.Params.TimeLimit = 600  # 允许求解器运行最长600秒
            model.Params.MIPGap = 0.001  # 允许0.1%的相对最优性差距
            # 如果你希望获得更多解（解池功能），可以设置如下参数：
            # model.Params.PoolSearchMode = 2
            # model.Params.PoolSolutions = 10

            # 构造决策变量：对于每个 SKU+级别分组中的每个候选方案建立一个二元变量
            decision_vars = {}
            grouped_result_df = combination_df.groupby(['product_sku_name', 'product_level_name'])
            for (sku, level), group in grouped_result_df:
                var_dict = {}
                for idx in group.index:
                    var_name = f"choice_{sku}_{level}_{idx}"
                    var_dict[idx] = model.addVar(vtype=GRB.BINARY, name=var_name)
                decision_vars[(sku, level)] = var_dict

            # 更新模型以整合新添加的变量
            model.update()

            # 目标函数：最小化总的付费成本
            objective_expr = gp.quicksum(
                decision_vars[(sku, level)][i] * combination_df.loc[i, 'payed_cost']
                for (sku, level), group in grouped_result_df for i in group.index
            )
            model.setObjective(objective_expr, GRB.MINIMIZE)

            # 添加约束：每个 SKU+级别只能选取一个定价方案
            for (sku, level), group in grouped_result_df:
                model.addConstr(
                    gp.quicksum(decision_vars[(sku, level)][i] for i in group.index) == 1,
                    name=f"OneChoice_{sku}_{level}"
                )

            # 定义总体成本与竞争力（基于原数据计算）
            total_cost = (df['success_nums'] * df['standard_price']).sum()
            total_competitive = df['inquiry_nums'].sum()

            # 约束：所选方案累计获得的竞争力需达到目标比例
            compete_gained_collect_expr = gp.quicksum(
                decision_vars[(sku, level)][i] * combination_df.loc[i, 'compete_gained']
                for (sku, level), group in grouped_result_df for i in group.index
            )
            model.addConstr(
                compete_gained_collect_expr >= target * total_competitive,
                name="CompetitivenessTarget"
            )
            # 求解模型
            model.optimize()

            # 如果没找到解，说明无法收敛，直接退出循环
            if model.status != GRB.OPTIMAL:
                logger.info("------------- 产品型号：{}, 竞争力：{}， 没找到解，直接退出循环 ---------------".format(product_name, target))

            # 2025-02-18 首先要有解，没解直接跳过
            # if model.status == GRB.OPTIMAL:
            logger.info("------------- 产品型号：{}, 竞争力：{} 找到解 ---------------".format(product_name, target))

            selected_indices = []
            # 遍历所有决策变量，提取取值为 1 的方案对应的候选方案 index
            for (sku, level), var_dict in decision_vars.items():
                for idx, var in var_dict.items():
                    if var.X > 0.5:  # 二元变量大于0.5视为选中
                        selected_indices.append(idx)
            selected_combine = combination_df.loc[selected_indices]

            # 按照 ahs_sku_name 和 ahs_level_name 进行分组，并统计 c2b_ppv_count 和 b2b_ppv_count 的和
            df_group = df.groupby(['product_name', 'product_sku_name', 'product_level_name', 'sku_level', 'sku_A+', 'finalprice'], as_index=False)[['inquiry_nums', 'success_nums']].sum()

            # 将sku-level级别的'inquiry_nums', 'success_nums' 加入 unique_combinations
            unique_combinations_re = pd.merge(unique_combinations, df_group, on=['product_sku_name', 'product_level_name'])
            adjusted_prices_df = pd.merge(selected_combine[['product_sku_name', 'product_level_name', 'adjusted_price']], unique_combinations_re, on=['product_sku_name', 'product_level_name'])
            adjusted_prices_df['level_order'] = adjusted_prices_df['product_level_name'].apply(lambda x: level_order.index(x))
            adjusted_prices_df = adjusted_prices_df.sort_values(by=['product_sku_name', 'level_order']).reset_index(drop=True)
            adjusted_prices_df['readjusted_price'] = adjusted_prices_df['adjusted_price']

            # 新版防倒挂：先进行平滑处理，再处理部分细分等级防倒挂
            def vlookup_custom(row):
                match = adjusted_prices_df[adjusted_prices_df['sku_level'] == row['sku_A+']]
                if not match.empty:
                    return match['adjusted_price'].tolist()[0]
                return None

            adjusted_prices_df['同sku的A+券前价'] = adjusted_prices_df.apply(vlookup_custom, axis=1)
            adjusted_prices_df['价差比'] = (adjusted_prices_df['adjusted_price'] - adjusted_prices_df['同sku的A+券前价']) / adjusted_prices_df['同sku的A+券前价'] * (-1)

            # # 旧版平滑
            # adjusted_prices_df['修差异过大后券前价格'] = adjusted_prices_df.apply(calculate_big_gap_value, axis=1)
            # adjusted_prices_df['修差前后gap'] = (adjusted_prices_df['修差异过大后券前价格'] - adjusted_prices_df['adjusted_price']).astype(int)

            # 新版价格平滑逻辑
            # 0. 获取每天sku-level策略价带来的 "可变平滑系数"
            ratios = []
            for sku in df['product_sku_name'].unique():
                sku_data = df[df['product_sku_name'] == sku]
                a_plus_price = sku_data[sku_data['product_level_name'] == 'A+']['finalprice'].values
                if len(a_plus_price) > 0:
                    a_plus_price = a_plus_price[0]
                else:
                    continue  # Skip if no A+ level price found
                for level in level_order:
                    level_data = sku_data[sku_data['product_level_name'] == level]
                    if len(level_data) > 0:
                        level_price = level_data['finalprice'].values[0]
                        percentage = level_price / a_plus_price
                    else:
                        percentage = None  # If no data for that level
                    ratios.append({
                        'product_sku_name': sku,
                        'product_level_name': level,
                        'percentage_of_A_plus': percentage
                    })
            ratio_df = pd.DataFrame(ratios)
            ratio_df_filtered = ratio_df.dropna(subset=['percentage_of_A_plus'])
            # 使用 apply 方法对每一行进行计算
            ratio_df_filtered['smooth_ratio'] = ratio_df_filtered.apply(
                lambda row: row['percentage_of_A_plus'] if row['product_level_name'] == 'A+'
                else row['percentage_of_A_plus'] - 0.03, axis=1
            )
            # 1. 合并两个数据框（基于 product_sku_name 和 product_level_name）
            adjusted_prices_df = pd.merge(
                adjusted_prices_df,
                ratio_df_filtered[['product_sku_name', 'product_level_name', 'smooth_ratio']],
                on=['product_sku_name', 'product_level_name'],
                how='left'
            )

            # 2. 定义一个函数，根据条件调整价格
            def adjusted_smooth_price(row):
                # 计算 adjusted_price 与 '同sku的A+券前价' 的比率
                ratio_val = row['adjusted_price'] / row['同sku的A+券前价']
                # 如果比率小于 smooth_ratio，则调整为 '同sku的A+券前价' * smooth_ratio，否则不变
                if ratio_val < row['smooth_ratio']:
                    return row['同sku的A+券前价'] * row['smooth_ratio']
                else:
                    return row['adjusted_price']

            # 3. 应用"可变平滑系数" 修差异过大后券前价格
            adjusted_prices_df['修差异过大后券前价格'] = adjusted_prices_df.apply(adjusted_smooth_price, axis=1)
            adjusted_prices_df['修差前后gap'] = (adjusted_prices_df['修差异过大后券前价格'] - adjusted_prices_df['adjusted_price']).astype(int)
            #######---新版平滑结束---######

            # 按照不同ahs_sku_name来处理，优先处理竞争力不下降，同时成本又降低的ahs_sku_name
            # 遍历每个sku
            container = []
            # 所有sku的倒挂都不处理的结果
            anti_prices = adjusted_prices_df["修差异过大后券前价格"].copy()
            adjusted_prices_df["anti_price"] = anti_prices  # 注意：这里只有部分sku的价格调整了
            df_anti0 = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price']],
                                on=['product_sku_name', 'product_level_name'], how='left')
            # Step 2: Calculate adjusted metrics for anti_price
            df_anti0['anti_price_couponed'] = df_anti0['anti_price'].apply(calculate_ahs_couponed_price)
            df_anti0['anti_price_gap'] = df_anti0['anti_price_couponed'] - df_anti0['ahs_couponed_price']
            df_anti0['anti_price_cost'] = df_anti0['anti_price_gap'] * df_anti0['success_nums']
            df_anti0['anti_competitive'] = df_anti0.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            anti_cost_rate0 = sum(df_anti0['anti_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
            anti_competitive0 = sum(df_anti0['anti_competitive']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
            # 计算倒挂次数
            adjusted_price_stats0 = calculate_inversion_statistics(adjusted_prices_df, 'adjusted_price')
            # 计算倒挂率
            anti_rate0 = sum(adjusted_price_stats0['inversion_count']) / sum(adjusted_price_stats0['total_comparisons']) if sum(adjusted_price_stats0['total_comparisons']) > 0 else 0
            dic = {
                'product_sku_name': '空',
                "anti_cost_rate": anti_cost_rate0,
                "anti_competitive": anti_competitive0,
                "anti_rate": anti_rate0,
                "anti_cost_rate_gap": 0,
                "anti_competitive_gap": 0,
                "anti_rate_gap": 0,
                "anti_gain_vs_competetive_loss": 0,
            }

            for sku_name in adjusted_prices_df['product_sku_name'].unique():

                # 对于每一次调价都需要初始化
                anti_prices = adjusted_prices_df["修差异过大后券前价格"].copy()
                # 遍历每个 "父等级-子等级" 映射关系
                for parent, child in level_hierarchy:
                    parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                    child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                    if parent_mask.sum() > 0 and child_mask.sum() > 0:
                        parent_idx = adjusted_prices_df[parent_mask].index[0]
                        child_idx = adjusted_prices_df[child_mask].index[0]
                        if anti_prices[parent_idx] <= anti_prices[child_idx]:
                            anti_prices[child_idx] = anti_prices[parent_idx] - MAX_ANTI_PRICE

                # 计算调整当前 sku_name 调价带来的 成本cost 和 竞争力competitive
                adjusted_prices_df["anti_price"] = anti_prices  # 注意：这里只有部分sku的价格调整了
                df_anti = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price']],
                                   on=['product_sku_name', 'product_level_name'], how='left')
                # Step 2: Calculate adjusted metrics for anti_price
                df_anti['anti_price_couponed'] = df_anti['anti_price'].apply(calculate_ahs_couponed_price)
                df_anti['anti_price_gap'] = df_anti['anti_price_couponed'] - df_anti['ahs_couponed_price']
                df_anti['anti_price_cost'] = df_anti['anti_price_gap'] * df_anti['success_nums']
                df_anti['anti_competitive'] = df_anti.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                anti_cost_rate = sum(df_anti['anti_price_cost']) / estimated_total_cost
                anti_competitive = sum(df_anti['anti_competitive']) / inquiry_nums_30d_total
                # 计算倒挂率
                adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'anti_price')
                anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                # 差值
                anti_cost_rate = anti_cost_rate0 - anti_cost_rate
                anti_rate_gap = anti_rate0 - anti_rate
                anti_competitive_gap = anti_competitive0 - anti_competitive

                dic = {
                    'product_sku_name': sku_name,
                    "anti_cost_rate": anti_cost_rate,
                    "anti_competitive": anti_competitive,
                    "anti_rate": anti_rate,
                    "anti_cost_rate_gap": anti_cost_rate,
                    "anti_competitive_gap": anti_competitive_gap,
                    "anti_rate_gap": anti_rate_gap,
                    "anti_gain_vs_competetive_loss": anti_rate_gap / anti_competitive_gap if anti_competitive_gap > 0 else 0
                }
                container.append(dic)

            df_set = pd.DataFrame(container)

            # 选出其中 anti_competitive最大 同时anti_cost_rate尽可能最小的 ahs_sku_name
            df_set_sorted = df_set.sort_values(by=['anti_competitive', 'anti_cost_rate'], ascending=[False, True])
            # 按照 anti_gain_vs_competetive_loss 排序
            df_set['sort_order'] = df_set['anti_competitive_gap'].apply(lambda x: 0 if x == 0 else 1)
            df_set_sorted2 = df_set.sort_values(by=['sort_order', 'anti_rate_gap', 'anti_gain_vs_competetive_loss'], ascending=[True, False, True]).drop(columns=['sort_order'])  # 删除辅助列

            sku_names_list = df_set_sorted['product_sku_name'].tolist()
            logger.info('---------------sku数量为：{} ---------------------'.format(len(sku_names_list)))

            # 方案1：部分倒挂策略1-寻找倒挂率小于人工倒挂率的部分倒挂组合
            for i in range(1, len(df_set_sorted)):

                sku_names_better = sku_names_list[:i]
                # 取头部几个sku进行防倒挂处理
                anti_prices_better = adjusted_prices_df["修差异过大后券前价格"].copy()
                # 遍历每个 "父等级-子等级" 映射关系
                for parent, child in level_hierarchy:
                    # 遍历每个sku
                    for sku_name in sku_names_better:
                        parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                        child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                        if parent_mask.sum() > 0 and child_mask.sum() > 0:
                            parent_idx = adjusted_prices_df[parent_mask].index[0]
                            child_idx = adjusted_prices_df[child_mask].index[0]
                            if anti_prices_better[parent_idx] <= anti_prices_better[child_idx]:
                                anti_prices_better[child_idx] = anti_prices_better[parent_idx] - MAX_ANTI_PRICE

                # 计算调整当前 sku_name 调价带来的 成本cost 和 竞争力competitive
                adjusted_prices_df["anti_price_opt"] = anti_prices_better  # 注意：这里只有部分sku的价格调整了
                df_anti = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price_opt']],
                                   on=['product_sku_name', 'product_level_name'], how='left')
                df_anti['anti_price_couponed'] = df_anti['anti_price_opt'].apply(calculate_ahs_couponed_price)
                df_anti['anti_price_gap'] = df_anti['anti_price_couponed'] - df_anti['ahs_couponed_price']
                df_anti['anti_price_cost'] = df_anti['anti_price_gap'] * df_anti['success_nums']
                df_anti['anti_competitive'] = df_anti.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                anti_cost_rate = sum(df_anti['anti_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
                anti_competitive = sum(df_anti['anti_competitive']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                # 计算倒挂率
                adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_opt')
                anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                anti_sku_cnt = '{}/{}'.format(len(sku_names_list), len(sku_names_list))

                # if anti_rate <= LIMIT_ANTI_RATE and anti_competitive >= human_compete_rate and anti_cost_rate <= human_cost_rate:
                if anti_rate <= LIMIT_ANTI_RATE:
                    anti_sku_cnt = '{}/{}'.format(len(sku_names_better), len(sku_names_list))
                    # logger.info('--------------anti_sku_cnt: {}, anti_rate: {}, LIMIT_ANTI_RATE: {}---------------------'.format(anti_sku_cnt, anti_rate, LIMIT_ANTI_RATE))
                    break
            # 方案1确定最终优化方案价格
            adjusted_prices_df["anti_price_better"] = anti_prices_better

            # 方案2：部分倒挂策略2-寻找倒挂率小于人工倒挂率的部分倒挂组合
            sku_names_list2 = df_set_sorted2['product_sku_name'].tolist()
            for i in range(1, len(df_set_sorted2)):

                sku_names_better2 = sku_names_list2[:i]
                # 取头部2个sku进行防倒挂处理
                anti_prices_better2 = adjusted_prices_df["修差异过大后券前价格"].copy()
                # 遍历每个 "父等级-子等级" 映射关系
                for parent, child in level_hierarchy:
                    # 遍历每个sku
                    for sku_name in sku_names_better2:
                        parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                        child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                        if parent_mask.sum() > 0 and child_mask.sum() > 0:
                            parent_idx = adjusted_prices_df[parent_mask].index[0]
                            child_idx = adjusted_prices_df[child_mask].index[0]
                            if anti_prices_better2[parent_idx] <= anti_prices_better2[child_idx]:
                                anti_prices_better2[child_idx] = anti_prices_better2[parent_idx] - MAX_ANTI_PRICE

                # 计算调整当前 sku_name 调价带来的 成本cost 和 竞争力competitive
                adjusted_prices_df["anti_price_opt2"] = anti_prices_better2  # 注意：这里只有部分sku的价格调整了
                df_anti = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price_opt2']],
                                   on=['product_sku_name', 'product_level_name'], how='left')
                df_anti['anti_price_couponed2'] = df_anti['anti_price_opt2'].apply(calculate_ahs_couponed_price)
                df_anti['anti_price_gap2'] = df_anti['anti_price_couponed2'] - df_anti['ahs_couponed_price']
                df_anti['anti_price_cost2'] = df_anti['anti_price_gap2'] * df_anti['success_nums']
                df_anti['anti_competitive2'] = df_anti.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                anti_cost_rate = sum(df_anti['anti_price_cost2']) / estimated_total_cost if estimated_total_cost > 0 else 0
                anti_competitive = sum(df_anti['anti_competitive2']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                # 计算倒挂率
                adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_opt2')
                anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                anti_sku_cnt2 = '{}/{}'.format(len(sku_names_list2), len(sku_names_list2))
                # if anti_rate <= LIMIT_ANTI_RATE and anti_competitive >= human_compete_rate and anti_cost_rate <= human_cost_rate:
                if anti_rate <= LIMIT_ANTI_RATE:
                    anti_sku_cnt2 = '{}/{}'.format(len(sku_names_better2), len(sku_names_list2))
                    logger.info('--------------anti_sku_cnt: {}, anti_rate: {}, LIMIT_ANTI_RATE: {}---------------------'.format(anti_sku_cnt, anti_rate, LIMIT_ANTI_RATE))
                    break
            # 方案2确定最终优化方案价格
            adjusted_prices_df["anti_price_better2"] = anti_prices_better2

            # 全部sku都进行全面的防倒挂处理
            readjusted_prices = adjusted_prices_df["修差异过大后券前价格"].copy()
            # 遍历每个 "父等级-子等级" 映射关系
            for parent, child in level_hierarchy:
                # 遍历每个sku
                for sku_name in adjusted_prices_df['product_sku_name'].unique():
                    parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                    child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                    if parent_mask.sum() > 0 and child_mask.sum() > 0:
                        parent_idx = adjusted_prices_df[parent_mask].index[0]
                        child_idx = adjusted_prices_df[child_mask].index[0]
                        if readjusted_prices[parent_idx] <= readjusted_prices[child_idx]:
                            readjusted_prices[child_idx] = readjusted_prices[parent_idx] - MAX_ANTI_PRICE

            # Calculate the final cost and price difference
            adjusted_prices_df["readjusted_price"] = readjusted_prices
            adjusted_prices_df = adjusted_prices_df.sort_values(by=['product_sku_name', "level_order"]).reset_index(drop=True)

            # 计算倒挂率
            adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'adjusted_price')
            smooth_price_stats = calculate_inversion_statistics(adjusted_prices_df, '修差异过大后券前价格')
            anti_price_stats_bt = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_better')
            anti_price_stats_bt2 = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_better2')
            readjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'readjusted_price')

            # 将 adjusted_price 映射回原来的 DataFrame
            df = df.merge(
                adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price_better', 'anti_price_better2', 'readjusted_price']],
                on=['product_sku_name', 'product_level_name'], how='left')

            # 计算调整后的券后价
            df['adjusted_price_couponed'] = df['adjusted_price'].apply(calculate_ahs_couponed_price)
            df['smooth_price_couponed'] = df['修差异过大后券前价格'].apply(calculate_ahs_couponed_price)
            df['anti_price_better_couponed'] = df['anti_price_better'].apply(calculate_ahs_couponed_price)
            df['anti_price_better_couponed2'] = df['anti_price_better2'].apply(calculate_ahs_couponed_price)
            df['readjusted_price_couponed'] = df['readjusted_price'].apply(calculate_ahs_couponed_price)

            # 总成本
            df['cost'] = df['standard_price'] * df['success_nums']
            df['cost_daily'] = df['standard_price'] * df['curr_success_nums']

            # 1次优化，添加 adjusted_price_gap, adjusted_price_cost,  cost, competitive_ori, competitive_after字段
            df['adjusted_price_gap'] = df['adjusted_price_couponed'] - df['ahs_couponed_price']
            df['adjusted_price_cost'] = df['adjusted_price_gap'] * df['success_nums']
            df['adjusted_price_cost_daily'] = df['adjusted_price_gap'] * df['curr_success_nums']  #
            df['adjusted_gap_rate'] = df.apply(lambda x: x['adjusted_price_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
            df['cost_rate'] = df.apply(lambda x: x['adjusted_price_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
            df['cost_rate_daily'] = df.apply(lambda x: x['adjusted_price_cost'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
            # 询价竞争力（券后）
            df['inquiry_competitive_after'] = df.apply(lambda x: x['inquiry_nums'] if x['adjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            ### 2025-03-26 新增
            # 成交竞争力（券后）
            df['success_competitive_after'] = df.apply(lambda x: x['success_nums'] if x['adjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            df['success_competitive_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['adjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

            # 1.5 价格平滑处理后的结果
            df['smooth_price_gap'] = df['smooth_price_couponed'] - df['ahs_couponed_price']
            df['smooth_price_cost'] = df['smooth_price_gap'] * df['success_nums']
            df['smooth_price_cost_daily'] = df['smooth_price_gap'] * df['curr_success_nums']  #
            df['smooth_gap_rate'] = df.apply(lambda x: x['smooth_price_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
            df['smooth_cost_rate'] = df.apply(lambda x: x['smooth_price_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
            df['smooth_cost_rate_daily'] = df.apply(lambda x: x['smooth_price_cost_daily'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
            # 询价竞争力（券后）
            df['inquiry_smooth_competitive_after'] = df.apply(lambda x: x['inquiry_nums'] if x['smooth_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            ### 2025-03-26 新增
            # 成交竞争力（券后）
            df['success_smooth_competitive_after'] = df.apply(lambda x: x['success_nums'] if x['smooth_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            df['success_smooth_competitive_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['smooth_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

            # 部分防倒挂：策略1
            df['anti_price_better_gap'] = df['anti_price_better_couponed'] - df['ahs_couponed_price']
            df['anti_price_better_cost'] = df['anti_price_better_gap'] * df['success_nums']
            df['anti_price_better_cost_daily'] = df['anti_price_better_gap'] * df['curr_success_nums']  #
            df['anti_gap_better_rate'] = df.apply(lambda x: x['anti_price_better_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
            df['anti_cost_better_rate'] = df.apply(lambda x: x['anti_price_better_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
            df['anti_cost_better_rate_daily'] = df.apply(lambda x: x['anti_price_better_cost_daily'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
            # 询价竞争力（券后）
            df['inquiry_anti_competitive_bt_after'] = df.apply(lambda x: x['inquiry_nums'] if x['anti_price_better_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            ### 2025-03-26 新增
            # 成交竞争力（券后）
            df['success_anti_competitive_bt_after'] = df.apply(lambda x: x['success_nums'] if x['anti_price_better_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            df['success_anti_competitive_bt_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['anti_price_better_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

            # 部分防倒挂：策略2
            df['anti_price_better_gap2'] = df['anti_price_better_couponed2'] - df['ahs_couponed_price']
            df['anti_price_better_cost2'] = df['anti_price_better_gap2'] * df['success_nums']
            df['anti_price_better_cost_daily2'] = df['anti_price_better_gap2'] * df['curr_success_nums']  #
            df['anti_gap_better_rate2'] = df.apply(lambda x: x['anti_price_better_gap2'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
            df['anti_cost_better_rate2'] = df.apply(lambda x: x['anti_price_better_cost2'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
            df['anti_cost_better_rate_daily2'] = df.apply(lambda x: x['anti_price_better_cost_daily2'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)
            # 询价竞争力（券后）
            df['inquiry_anti_competitive_bt_after2'] = df.apply(lambda x: x['inquiry_nums'] if x['anti_price_better_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            ### 2025-03-26 新增
            # 成交竞争力（券后）
            df['success_anti_competitive_bt_after2'] = df.apply(lambda x: x['success_nums'] if x['anti_price_better_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            df['success_anti_competitive_bt_after_daily2'] = df.apply(lambda x: x['curr_success_nums'] if x['anti_price_better_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

            # 最终修正后的结果
            df['readjusted_price_gap'] = df['readjusted_price_couponed'] - df['ahs_couponed_price']
            df['readjusted_price_cost'] = df['readjusted_price_gap'] * df['success_nums']
            df['readjusted_price_cost_daily'] = df['readjusted_price_gap'] * df['curr_success_nums']  #
            df['readjusted_gap_rate'] = df.apply(lambda x: x['readjusted_price_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
            df['recost_rate'] = df.apply(lambda x: x['readjusted_price_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
            df['recost_rate_daily'] = df.apply(lambda x: x['readjusted_price_cost_daily'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
            # 询价竞争力（券后）
            df['inquiry_recompetitive_after'] = df.apply(lambda x: x['inquiry_nums'] if x['readjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            ### 2025-03-26 新增
            # 成交竞争力（券后）
            df['success_recompetitive_after'] = df.apply(lambda x: x['success_nums'] if x['readjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
            df['success_recompetitive_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['readjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

            # 对结果进行排序
            df = df.sort_values(by=['product_sku_name', 'level_order', 'zz_strategy_price'], ascending=[True, True, False]).reset_index(drop=True)

            # df_container[str(target)] = df
            df_container[target] = df

            # 预估成本率(30天)
            real_total_cost_rate = sum(df['adjusted_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
            smooth_total_cost_rate = sum(df['smooth_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
            anti_total_cost_bt_rate = sum(df['anti_price_better_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
            anti_total_cost_bt_rate2 = sum(df['anti_price_better_cost2']) / estimated_total_cost if estimated_total_cost > 0 else 0
            re_real_total_cost_rate = sum(df['readjusted_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0

            # 当日真实成本率
            real_total_cost_rate_daily = sum(df['adjusted_price_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
            smooth_total_cost_rate_daily = sum(df['smooth_price_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
            anti_total_cost_bt_rate_daily = sum(df['anti_price_better_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
            anti_total_cost_bt_rate_daily2 = sum(df['anti_price_better_cost_daily2']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
            re_real_total_cost_rate_daily = sum(df['readjusted_price_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0

            # 询价竞争力
            inquiry_real_total_compete_rate = sum(df['inquiry_competitive_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
            inquiry_smooth_total_compete_rate = sum(df['inquiry_smooth_competitive_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
            inquiry_anti_total_compete_bt_rate = sum(df['inquiry_anti_competitive_bt_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
            inquiry_anti_total_compete_bt_rate2 = sum(df['inquiry_anti_competitive_bt_after2']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
            inquiry_re_real_total_compete_rate = sum(df['inquiry_recompetitive_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0

            ###  2025-03-26 新增
            # 成交竞争力（30天预估）
            success_real_total_compete_rate = sum(df['success_competitive_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
            success_smooth_total_compete_rate = sum(df['success_smooth_competitive_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
            success_anti_total_compete_bt_rate = sum(df['success_anti_competitive_bt_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
            success_anti_total_compete_bt_rate2 = sum(df['success_anti_competitive_bt_after2']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
            success_re_real_total_compete_rate = sum(df['success_recompetitive_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
            # 成交竞争力（实际）
            success_real_total_compete_rate_daily = sum(df['success_competitive_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
            success_smooth_total_compete_rate_daily = sum(df['success_smooth_competitive_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
            success_anti_total_compete_bt_rate_daily = sum(df['success_anti_competitive_bt_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
            success_anti_total_compete_bt_rate_daily2 = sum(df['success_anti_competitive_bt_after_daily2']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
            success_re_real_total_compete_rate_daily = sum(df['success_recompetitive_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0

            # 倒挂率
            anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
            smooth_anti_rate = sum(smooth_price_stats['inversion_count']) / sum(smooth_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
            real_anti_bt_rate = sum(anti_price_stats_bt['inversion_count']) / sum(anti_price_stats_bt['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
            real_anti_bt_rate2 = sum(anti_price_stats_bt2['inversion_count']) / sum(anti_price_stats_bt2['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
            reanti_rate = sum(readjusted_price_stats['inversion_count']) / sum(readjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0

            results.append({
                'inquiry_nums_30d_total': inquiry_nums_30d_total,
                'success_nums_30d_total': success_nums_30d_total,
                'success_nums_daily_total': success_nums_daily_total,  #
                'target': f"{target:.2%}",
                'product_name': product_name,
                'estimated_total_cost': estimated_total_cost,
                'real_total_cost_daily': real_total_cost_daily,  #
                # 模型结果
                'anti_rate': '0',
                'real_total_cost_rate': f"{real_total_cost_rate:.2%}",
                'real_total_cost_rate_daily': f"{real_total_cost_rate_daily:.2%}",  #
                'inquiry_real_total_compete_rate': f"{inquiry_real_total_compete_rate:.2%}",
                'success_real_total_compete_rate': f"{success_real_total_compete_rate:.2%}",
                'success_real_total_compete_rate_daily': f"{success_real_total_compete_rate_daily:.2%}",
                # 平滑后结果
                'smooth_anti_rate': '0',
                'smooth_total_cost_rate': f"{smooth_total_cost_rate:.2%}",
                'smooth_total_cost_rate_daily': f"{smooth_total_cost_rate_daily:.2%}",  #
                'inquiry_smooth_total_compete_rate': f"{inquiry_smooth_total_compete_rate:.2%}",
                'success_smooth_total_compete_rate': f"{success_smooth_total_compete_rate:.2%}",
                'success_smooth_total_compete_rate_daily': f"{success_smooth_total_compete_rate_daily:.2%}",
                # 低于人工的多个sku防倒挂：策略1
                'anti_sku_cnt': anti_sku_cnt,
                'real_anti_bt_rate': f"{real_anti_bt_rate:.2%}",
                'anti_total_cost_bt_rate': f"{anti_total_cost_bt_rate:.2%}",
                'anti_total_cost_bt_rate_daily': f"{anti_total_cost_bt_rate_daily:.2%}",  #
                'inquiry_anti_total_compete_bt_rate': f"{inquiry_anti_total_compete_bt_rate:.2%}",
                'success_anti_total_compete_bt_rate': f"{success_anti_total_compete_bt_rate:.2%}",
                'success_anti_total_compete_bt_rate_daily': f"{success_anti_total_compete_bt_rate_daily:.2%}",
                # 低于人工的多个sku防倒挂：策略2
                'anti_sku_cnt2': anti_sku_cnt2,
                'real_anti_bt_rate2': f"{real_anti_bt_rate2:.2%}",
                'anti_total_cost_bt_rate2': f"{anti_total_cost_bt_rate2:.2%}",
                'anti_total_cost_bt_rate_daily2': f"{anti_total_cost_bt_rate_daily2:.2%}",  #
                'inquiry_anti_total_compete_bt_rate2': f"{inquiry_anti_total_compete_bt_rate2:.2%}",
                'success_anti_total_compete_bt_rate2': f"{success_anti_total_compete_bt_rate2:.2%}",
                'success_anti_total_compete_bt_rate_daily2': f"{success_anti_total_compete_bt_rate_daily2:.2%}",
                # 全部防倒挂
                'reanti_rate': '0',
                're_real_total_cost_rate': f"{re_real_total_cost_rate:.2%}",
                're_real_total_cost_rate_daily': f"{re_real_total_cost_rate_daily:.2%}",
                'inquiry_re_real_total_compete_rate': f"{inquiry_re_real_total_compete_rate:.2%}",
                'success_re_real_total_compete_rate': f"{success_re_real_total_compete_rate:.2%}",
                'success_re_real_total_compete_rate_daily': f"{success_re_real_total_compete_rate_daily:.2%}"
            })

            ### 2025-03-26 新增
            #   1、基础数据：询价量、成交量
            #   2、目标数据：询价竞争力、成交竞争力、ahs追后券后价、zz追后券后价、价差金额（ahs-zz）、价差比（（ahs-zz/zz）-1）
            # AHS
            # ahs_adjust_weighted_price = int( sum(df['adjusted_price_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))
            ahs_smooth_weighted_price = int(sum(df['smooth_price_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # AHS权重价格1
            ahs_anti1_weighted_price = int(sum(df['anti_price_better_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # AHS权重价格2
            ahs_anti2_weighted_price = int(sum(df['anti_price_better_couponed2'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # AHS权重价格3
            ahs_readjust_weighted_price = int(sum(df['readjusted_price_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # AHS权重价格4
            zz_weighted_price = int(sum(df['zz_couponed_price_18_added'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # ZZ 权重价格

            # 2025-04-01 增加3个拆分成本率：Bi调价成本率、平滑成本率、倒挂成本率
            bi_estimated_splited_cost_rate = sum((df['adjusted_price_couponed'] - df['ahs_couponed_price']) * df['success_nums']) / estimated_total_cost if estimated_total_cost > 0 else 0
            smooth_estimated_splited_cost_rate = sum((df['smooth_price_couponed'] - df['adjusted_price_couponed']) * df['success_nums']) / estimated_total_cost if estimated_total_cost > 0 else 0

            # logger.info('-----------LIMIT_ANTI_RATE: {}, smooth_anti_rate: {}-----------------'.format(LIMIT_ANTI_RATE, smooth_anti_rate))
            if LIMIT_ANTI_RATE >= smooth_anti_rate:
                candidates.append({
                    'partition_date': target_date,
                    'brand_name': product_brand_name,
                    'product_name': product_name,
                    'estimated_total_cost': estimated_total_cost,
                    'real_total_cost_daily': real_total_cost_daily,  #
                    'target': target,
                    'type': 'smooth',
                    'anti_rate': smooth_anti_rate,
                    'cost_rate_estimated': smooth_total_cost_rate,
                    'cost_rate_real': smooth_total_cost_rate_daily,
                    'inquiry_compete_rate': inquiry_smooth_total_compete_rate,
                    'success_compete_rate': success_smooth_total_compete_rate,
                    'success_compete_rate_daily': success_smooth_total_compete_rate_daily,
                    # 2025-03-26 新增
                    'inquiry_nums_30d_total': inquiry_nums_30d_total,  # 30天询价量
                    'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                    'ahs_weighted_price': ahs_smooth_weighted_price,
                    'zz_weighted_price': zz_weighted_price,
                    'weited_price_gap': ahs_smooth_weighted_price - zz_weighted_price,
                    'weited_price_gap_rate': ((ahs_smooth_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                })

            if LIMIT_ANTI_RATE >= real_anti_bt_rate:
                candidates.append({
                    'partition_date': target_date,
                    'brand_name': product_brand_name,
                    'product_name': product_name,
                    'estimated_total_cost': estimated_total_cost,
                    'real_total_cost_daily': real_total_cost_daily,  #
                    'target': target,
                    'type': 'anti1',
                    'anti_rate': real_anti_bt_rate,
                    'cost_rate_estimated': anti_total_cost_bt_rate,
                    'cost_rate_real': anti_total_cost_bt_rate_daily,
                    'inquiry_compete_rate': inquiry_anti_total_compete_bt_rate,
                    'success_compete_rate': success_anti_total_compete_bt_rate,  # ???
                    'success_compete_rate_daily': success_anti_total_compete_bt_rate_daily,
                    # 2025-03-26 新增
                    'inquiry_nums_30d_total': inquiry_nums_30d_total,  # 30天询价量
                    'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                    'ahs_weighted_price': ahs_anti1_weighted_price,
                    'zz_weighted_price': zz_weighted_price,
                    'weited_price_gap': ahs_anti1_weighted_price - zz_weighted_price,
                    'weited_price_gap_rate': ((ahs_anti1_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                })

            # logger.info('-----------LIMIT_ANTI_RATE: {}, real_anti_bt_rate2: {}-----------------'.format(LIMIT_ANTI_RATE, real_anti_bt_rate2))
            if LIMIT_ANTI_RATE >= real_anti_bt_rate2:
                candidates.append({
                    'partition_date': target_date,
                    'brand_name': product_brand_name,
                    'product_name': product_name,
                    'estimated_total_cost': estimated_total_cost,
                    'real_total_cost_daily': real_total_cost_daily,  #
                    'target': target,
                    'type': 'anti2',
                    'anti_rate': real_anti_bt_rate2,
                    'cost_rate_estimated': anti_total_cost_bt_rate2,
                    'cost_rate_real': anti_total_cost_bt_rate_daily2,
                    'inquiry_compete_rate': inquiry_anti_total_compete_bt_rate2,
                    'success_compete_rate': success_anti_total_compete_bt_rate2,
                    'success_compete_rate_daily': success_anti_total_compete_bt_rate_daily2,
                    # 2025-03-26 新增
                    'inquiry_nums_30d_total': inquiry_nums_30d_total,  # 30天询价量
                    'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                    'ahs_weighted_price': ahs_anti2_weighted_price,
                    'zz_weighted_price': zz_weighted_price,
                    'weited_price_gap': ahs_anti2_weighted_price - zz_weighted_price,
                    'weited_price_gap_rate': ((ahs_anti2_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                })

            # logger.info('-----------LIMIT_ANTI_RATE: {}, reanti_rate: {}-----------------'.format(LIMIT_ANTI_RATE, reanti_rate))
            if LIMIT_ANTI_RATE >= reanti_rate:
                candidates.append({
                    'partition_date': target_date,
                    'brand_name': product_brand_name,
                    'product_name': product_name,
                    'estimated_total_cost': estimated_total_cost,
                    'real_total_cost_daily': real_total_cost_daily,  #
                    'target': target,
                    'type': 'reanti',
                    'anti_rate': reanti_rate,
                    'cost_rate_estimated': re_real_total_cost_rate,
                    'cost_rate_real': re_real_total_cost_rate_daily,
                    'inquiry_compete_rate': inquiry_re_real_total_compete_rate,
                    'success_compete_rate': success_re_real_total_compete_rate,
                    'success_compete_rate_daily': success_re_real_total_compete_rate_daily,
                    # 2025-03-26 新增
                    'inquiry_nums_30d_total': inquiry_nums_30d_total,  # 30天询价量
                    'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                    'ahs_weighted_price': ahs_readjust_weighted_price,
                    'zz_weighted_price': zz_weighted_price,
                    'weited_price_gap': ahs_readjust_weighted_price - zz_weighted_price,
                    'weited_price_gap_rate': ((ahs_readjust_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                })

            candidates_df = pd.DataFrame(candidates)

            # 2025-04-01 新增3个拆分成本率
            candidates_df['bi_estimated_splited_cost_rate'] = bi_estimated_splited_cost_rate
            candidates_df['smooth_estimated_splited_cost_rate'] = smooth_estimated_splited_cost_rate
            candidates_df['anti_estimated_splited_cost_rate'] = candidates_df['cost_rate_estimated'] - bi_estimated_splited_cost_rate - smooth_estimated_splited_cost_rate
            logger.info("-----------candidates_df的数量： {}---------------\n".format(len(candidates_df)))
            logger.info("\n-----------candidates_df的columns： {}---------------\n".format(candidates_df.columns))

            if len(candidates_df) == 0:
                logger.info('---------------没找到符合倒挂率的候选集candidates-----------------')

            # 2025-02-18 从当前竞争力中的候选集中，选择成本最小的那个
            candidates_df_sorted = candidates_df.sort_values(by='cost_rate_estimated', ascending=True)
            candidates_head = candidates_df_sorted.head(1)

            # 从 candidates_df_sorted 中选出第一条数据并合并到 chosen_df
            chosen_df = pd.concat([chosen_df, candidates_head], ignore_index=True)

            # 输出选中的candidates中的 target 和 type
            chosen_candidate_target = candidates_df_sorted['target'].tolist()[0]
            chosen_candidate_type = candidates_df_sorted['type'].tolist()[0]

            # the_df = df_container[chosen_candidate_target]
            the_df = df  # 2025-02-18 从当前df中筛选出相应的数据结果 通过type来选定最终的df取值

            # 2025-04-01 增加5个价格和3个tag, 用于区分是否BI调价、是否平滑、是否防倒挂
            the_df['bi_price'] = the_df['adjusted_price']
            the_df['smooth_price'] = the_df['修差异过大后券前价格']
            the_df['anti1_price'] = the_df['anti_price_better']
            the_df['anti2_price'] = the_df['anti_price_better2']
            the_df['reanti_price'] = the_df['readjusted_price']

            # the_df['bi_tag'] = 1 if the_df['adjusted_price'] == the_df['finalprice'] else 0
            # the_df['smooth_tag'] = 1 if the_df['修差异过大后券前价格'] == the_df['adjusted_price'] else 0
            the_df['bi_tag'] = np.where(the_df['adjusted_price'] == the_df['finalprice'], 1, 0)
            the_df['smooth_tag'] = np.where(the_df['修差异过大后券前价格'] == the_df['adjusted_price'], 1, 0)
            # the_df['deanti_tag'] = 0  补在后面

            if chosen_candidate_type == 'smooth':
                the_df['chosen_type'] = 'smooth'
                the_df['chosen_price'] = the_df['修差异过大后券前价格']
                the_df['chosen_price_couponed'] = the_df['smooth_price_couponed']
                the_df['chosen_price_gap'] = the_df['smooth_price_gap']
                the_df['chosen_price_cost'] = the_df['smooth_price_cost']
                the_df['chosen_price_cost_daily'] = the_df['smooth_price_cost_daily']
                the_df['chosen_gap_rate'] = the_df['smooth_gap_rate']
                the_df['chosen_cost_rate'] = the_df['smooth_cost_rate']
                the_df['chosen_cost_rate_daily'] = the_df['smooth_cost_rate_daily']
                the_df['chosen_competetive'] = the_df['inquiry_smooth_competitive_after']
                the_df['chosen_competetive_success'] = the_df['success_smooth_competitive_after']
                the_df['chosen_competetive_success_daily'] = the_df['success_smooth_competitive_after_daily']
            if chosen_candidate_type == 'anti1':
                the_df['chosen_type'] = 'anti1'
                the_df['chosen_price'] = the_df['anti_price_better']
                the_df['chosen_price_couponed'] = the_df['anti_price_better_couponed']
                the_df['chosen_price_gap'] = the_df['anti_price_better_gap']
                the_df['chosen_price_cost'] = the_df['anti_price_better_cost']
                the_df['chosen_price_cost_daily'] = the_df['anti_price_better_cost_daily']
                the_df['chosen_gap_rate'] = the_df['anti_gap_better_rate']
                the_df['chosen_cost_rate'] = the_df['anti_cost_better_rate']
                the_df['chosen_cost_rate_daily'] = the_df['anti_cost_better_rate_daily']
                the_df['chosen_competetive'] = the_df['inquiry_anti_competitive_bt_after']
                the_df['chosen_competetive_success'] = the_df['success_anti_competitive_bt_after']
                the_df['chosen_competetive_success_daily'] = the_df['success_anti_competitive_bt_after_daily']
            if chosen_candidate_type == 'anti2':
                the_df['chosen_type'] = 'anti2'
                the_df['chosen_price'] = the_df['anti_price_better2']
                the_df['chosen_price_couponed'] = the_df['anti_price_better_couponed2']
                the_df['chosen_price_gap'] = the_df['anti_price_better_gap2']
                the_df['chosen_price_cost'] = the_df['anti_price_better_cost2']
                the_df['chosen_price_cost_daily'] = the_df['anti_price_better_cost_daily2']
                the_df['chosen_gap_rate'] = the_df['anti_gap_better_rate2']
                the_df['chosen_cost_rate'] = the_df['anti_cost_better_rate2']
                the_df['chosen_cost_rate_daily'] = the_df['anti_cost_better_rate_daily2']
                the_df['chosen_competetive'] = the_df['inquiry_anti_competitive_bt_after2']
                the_df['chosen_competetive_success'] = the_df['success_anti_competitive_bt_after2']
                the_df['chosen_competetive_success_daily'] = the_df['success_anti_competitive_bt_after_daily2']
            if chosen_candidate_type == 'reanti':
                the_df['chosen_type'] = 'reanti'
                the_df['chosen_price'] = the_df['readjusted_price']
                the_df['chosen_price_couponed'] = the_df['readjusted_price_couponed']
                the_df['chosen_price_gap'] = the_df['readjusted_price_gap']
                the_df['chosen_price_cost'] = the_df['readjusted_price_cost']
                the_df['chosen_price_cost_daily'] = the_df['readjusted_price_cost_daily']
                the_df['chosen_gap_rate'] = the_df['readjusted_gap_rate']
                the_df['chosen_cost_rate'] = the_df['recost_rate']
                the_df['chosen_cost_rate_daily'] = the_df['recost_rate_daily']
                the_df['chosen_competetive'] = the_df['inquiry_recompetitive_after']
                the_df['chosen_competetive_success'] = the_df['success_recompetitive_after']
                the_df['chosen_competetive_success_daily'] = the_df['success_recompetitive_after_daily']

            # 接上面新增的tag
            # the_df['deanti_tag'] = 1 if the_df['chosen_price'] == the_df['修差异过大后券前价格'] else 0
            the_df['deanti_tag'] = np.where(the_df['chosen_price'] == the_df['修差异过大后券前价格'], 1, 0)

            chosen_detailed_df = pd.concat([chosen_detailed_df, the_df], ignore_index=True)

            # if len(the_df) > 0:
            sku_level = the_df[['partition_date', 'target', 'product_brand_name', 'product_name', 'product_sku_name',
                                'product_level_name', 'product_id', 'product_sku_id', 'product_level_id',
                                'finalprice', 'ahs_couponed_price', 'standard_price', 'chosen_price', 'chosen_price_couponed',
                                # 2025-04-01 增加5个价格和3个tag
                                'bi_price', 'smooth_price', 'anti1_price', 'anti2_price', 'reanti_price', 'bi_tag', 'smooth_tag', 'deanti_tag']]
            sku_level['group_name'] = '分城定价-1组-BD'
            sku_level['group_id'] = 219

            # 通过 partition_date + sku + level + group 去重
            sku_level.drop_duplicates(inplace=True)
            # 拼接sku-level的价格进入
            sku_level_df = pd.concat([sku_level_df, sku_level], ignore_index=True)

            selected_chosen_results = chosen_detailed_df[['partition_date', 'target', 'product_brand_name', 'product_id', 'product_name',
             'product_level_id', 'product_level_name', 'product_sku_id', 'product_sku_name', 'curr_success_nums', 'success_nums', 'inquiry_nums',
             'zz_pre_coupon_price', 'zz_couponed_price_18_added', 'zz_strategy_price', 'zz_strategy_price_couponed', 'human_price', 'finalprice',
             'ahs_couponed_price',
             'chosen_price', 'chosen_price_couponed', 'chosen_price_gap', 'chosen_price_cost', 'chosen_price_cost_daily', 'chosen_gap_rate', 'chosen_cost_rate',
             'chosen_cost_rate_daily', 'chosen_competetive',
             'chosen_competetive_success', 'chosen_competetive_success_daily'  # 2025-03-26 新增
            ]]

            # 为简化示例，这里只返回数据
            return {
                "success": True,
                "message": "成功获取产品数据",
                "data": chosen_df,
                "sku_level_df": sku_level_df,
                "selected_chosen_results": selected_chosen_results
            }
            
        except Exception as e:
            return {
                "success": False,
                "message": f"查询产品数据时出错: {e}",
                "data": None
            }
            
    except Exception as e:
        return {
            "success": False,
            "message": f"处理时出错: {e}",
            "data": None
        }

# Run price strategy calculation
def run_price_strategy_calculation():
    try:
        # Create required directories if they don't exist
        os.makedirs('collections', exist_ok=True)
        os.makedirs('sku_level', exist_ok=True)
        
        # Run the script
        result = subprocess.run(['python', 'price_strategy_daily_system_PRODUCT_today_iphone.py'], 
                               capture_output=True, text=True)
        
        if result.returncode == 0:
            st.success("价格策略计算成功完成!")
            return True
        else:
            st.error(f"价格策略计算失败: {result.stderr}")
            return False
    except Exception as e:
        st.error(f"执行错误: {e}")
        return False

# Run data statistics calculation
def run_data_statistics_calculation():
    try:
        result = subprocess.run(['python', 'data_statistics_system_PRODUCT_today_iphone.py'], 
                               capture_output=True, text=True)
        
        if result.returncode == 0:
            st.success("数据统计计算成功完成!")
            return True
        else:
            st.error(f"数据统计计算失败: {result.stderr}")
            return False
    except Exception as e:
        st.error(f"执行错误: {e}")
        return False

# 添加线程安全的Streamlit更新函数
def update_progress_safely(progress_bar, value):
    """安全地在多线程环境中更新Streamlit进度条"""
    try:
        progress_bar.progress(value)
    except Exception as e:
        print(f"更新进度条出错: {str(e)}")

# Main application
def main():
    # 不再需要在这里重复设置页面标题，因为已经在页面配置中设置了
    # st.title("多品类价格策略分析平台")
    
    # 生成唯一标识符，用于区分不同会话的UI元素
    session_id = str(uuid.uuid4())[:8]
    
    # 检查是否有之前的处理结果
    if 'processed_data' in st.session_state:
        show_processed_results = st.checkbox("显示之前的处理结果", value=True)
        if show_processed_results:
            # 从session state中恢复数据
            data = st.session_state['processed_data']
            combined_selected_results = data.get('combined_selected_results')
            combined_sku_level_df = data.get('combined_sku_level_df')
            combined_chosen_df = data.get('combined_chosen_df')
            successful_products = data.get('successful_products', [])
            product_filter = data.get('product_filter', [])
            bi_date = data.get('bi_date')
            product_results = data.get('product_results', {})
            bi_products = data.get('bi_products', [])
            bi_competitiveness_dict = data.get('bi_competitiveness_dict', {})
            
            # 显示之前的处理结果
            st.header("处理结果")
            
            # 汇总统计信息
            col1, col2, col3 = st.columns(3)
            
            with col1:
                st.metric("处理产品数", len(successful_products))
            
            with col2:
                if combined_selected_results is not None:
                    total_sku = len(combined_selected_results)
                    st.metric("总数据量", total_sku)
            
            with col3:
                if combined_chosen_df is not None:
                    avg_competitiveness = combined_chosen_df['target'].astype(str).str.rstrip('%').astype(float).mean() / 100
                    st.metric("平均目标竞争力", f"{avg_competitiveness:.1%}")
            
            # 创建一个选项卡，便于查看不同级别的数据
            data_tabs = st.tabs(["策略数据摘要", "SKU-LEVEL级别数据", "PPV级别数据"])
            
            # 创建产品筛选器（全局共享）
            product_filter = st.multiselect(
                "按产品筛选", 
                options=successful_products,
                default=product_filter,
                key=f"previous_product_filter_{session_id}"  # 添加会话ID以确保唯一性
            )
            
            # 策略数据摘要选项卡 (放在第一个位置)
            with data_tabs[0]:
                if combined_chosen_df is not None:
                    # 筛选数据
                    if product_filter:
                        if 'product_name' in combined_chosen_df.columns:
                            filtered_chosen_df = combined_chosen_df[combined_chosen_df['product_name'].isin(product_filter)]
                        else:
                            filtered_chosen_df = combined_chosen_df[combined_chosen_df['product_name_source'].isin(product_filter)]
                        
                        # 显示筛选后的详细结果
                        st.dataframe(filtered_chosen_df)
                        
                        # 提供下载按钮
                        if filtered_chosen_df is not None and not filtered_chosen_df.empty:
                            csv_data = filtered_chosen_df.to_csv(index=False).encode('utf-8')
                            st.download_button(
                                label="下载策略数据摘要",
                                data=csv_data,
                                file_name=f"chosen_df_summary_{bi_date}.csv",
                                mime="text/csv",
                                key=f"previous_download_summary_data_{session_id}"  # 添加会话ID以确保唯一性
                            )
                        else:
                            st.write("策略数据摘要不可用")
            
            # SKU-LEVEL级别数据选项卡 (放在第二个位置)
            with data_tabs[1]:
                if combined_sku_level_df is not None:
                    # 筛选数据
                    if product_filter:
                        if 'product_name' in combined_sku_level_df.columns:
                            filtered_sku_level_df = combined_sku_level_df[combined_sku_level_df['product_name'].isin(product_filter)]
                        else:
                            filtered_sku_level_df = combined_sku_level_df[combined_sku_level_df['product_name_source'].isin(product_filter)]
                        
                        # 显示筛选后的详细结果
                        st.dataframe(filtered_sku_level_df)
                        
                        # 提供下载按钮
                        if filtered_sku_level_df is not None and not filtered_sku_level_df.empty:
                            csv_data = filtered_sku_level_df.to_csv(index=False).encode('utf-8')
                            st.download_button(
                                label="下载SKU-LEVEL级别数据",
                                data=csv_data,
                                file_name=f"sku_level_data_{bi_date}.csv",
                                mime="text/csv",
                                key=f"previous_download_sku_data_{session_id}"  # 添加会话ID以确保唯一性
                            )
                        else:
                            st.write("SKU-LEVEL级别数据不可用")
            
            # PPV级别数据选项卡 (放在第三个位置)
            with data_tabs[2]:
                if combined_selected_results is not None:
                    # 筛选数据
                    if product_filter:
                        if 'product_name' in combined_selected_results.columns:
                            filtered_results = combined_selected_results[combined_selected_results['product_name'].isin(product_filter)]
                        else:
                            filtered_results = combined_selected_results[combined_selected_results['product_name_source'].isin(product_filter)]
                        
                        # 显示筛选后的详细结果
                        st.dataframe(filtered_results)
                        
                        # 提供下载按钮
                        if filtered_results is not None and not filtered_results.empty:
                            csv_data = filtered_results.to_csv(index=False).encode('utf-8')
                            st.download_button(
                                label="下载PPV级别数据",
                                data=csv_data,
                                file_name=f"ppv_level_data_{bi_date}.csv",
                                mime="text/csv",
                                key=f"previous_download_ppv_data_{session_id}"  # 添加会话ID以确保唯一性
                            )
                        else:
                            st.write("PPV级别数据不可用")
            
            # 添加一个分隔符，将之前的结果与新的输入分开
            st.divider()
            st.write("要处理新的数据，请在下面选择参数并点击'运行'按钮。")
    
    # 移除会话上下文获取相关代码
    # 获取当前的Streamlit运行上下文
    session_state = st.session_state
    
    # Header
    st.title("多品类价格策略分析平台")
    
    # 定义在主界面上需要的变量
    current_date = datetime.datetime.now().date()
    current_datetime = datetime.datetime.now()
    
    # 使用缓存加载各种产品名称列表
    @st.cache_data(ttl=3600)  # 缓存1小时
    def load_product_names(category, subcategory=None):
        if category == "手机":
            if subcategory == "苹果":
                return [
                    '苹果 iPhone 11', '苹果 iPhone 12', '苹果 iPhone 13', '苹果 iPhone 14', '苹果 iPhone 15', '苹果 iPhone 16',
                    '苹果 iPhone 11 Pro', '苹果 iPhone 12 Pro', '苹果 iPhone 13 Pro', '苹果 iPhone 14 Pro', '苹果 iPhone 15 Pro', '苹果 iPhone 16 Pro',
                    '苹果 iPhone 11 Pro Max', '苹果 iPhone 12 Pro Max', '苹果 iPhone 13 Pro Max', '苹果 iPhone 14 Pro Max', '苹果 iPhone 15 Pro Max', '苹果 iPhone 16 Pro Max',
                ]
            elif subcategory == "安卓":
                return [
                    'Redmi K60', '小米 13', '华为 Mate 60 Pro', 'Redmi Note 12 Turbo', '华为 Mate 40 Pro (5G版)', '三星 Galaxy S23 Ultra', '华为 Mate 30 Pro (5G版)',
                    'Redmi K70', '小米 14', 'Redmi K40', '华为 Pocket 2', '华为 P40 Pro', '华为 P40 (5G版)', '华为 P30 Pro', 'Redmi K60 (至尊版)', '华为 Mate 60',
                    '华为 Mate 60 Pro+', '荣耀 70 (5G版)', '华为 Mate 50', '小米 11', '小米 10S', '华为 P60', '小米 13 Pro', '荣耀 X50', '小米 10 (5G版)', '一加 Ace 2',
                    '华为 Mate 50 Pro', '小米 13 Ultra', '华为 Mate X5', '华为 Mate 30 (5G版)', 'iQOO Neo5', '小米 14 Pro', 'iQOO Neo9', 'Redmi Note 11T Pro',
                    'Redmi K50', 'Redmi K70 (至尊版)', 'Redmi K70 Pro', '华为 P30', '小米 11 Ultra', 'OPPO Find X6 Pro', 'Redmi K50 (5G 至尊版)', 'iQOO Neo8',
                    'vivo X100', '三星 Galaxy Z Fold5 (5G版)', '小米 MIX Flip', 'Redmi Note 10 Pro', '一加 Ace 2 Pro', '荣耀 50', 'iQOO 12', '一加 12',
                    '华为 P50 Pro (骁龙版)', 'Redmi Note 8 Pro', 'Redmi Note 13 Pro', 'Redmi Note 9 Pro (5G版)', '荣耀 90', '华为 Mate 40', '荣耀 9X', 'iQOO 11',
                    '一加 Ace 3', 'Redmi Note 11 (5G版)', 'vivo X90', '华为 P60 Pro', 'vivo X80', 'Redmi K70E', '小米 12', 'Redmi K40 (游戏增强版)', 'OPPO Find X7',
                    '小米 14 Ultra', '荣耀 Magic6 Pro', 'vivo X100 Pro', '华为 nova 7 (5G版)', '一加 11', '三星 Galaxy S24 Ultra', '华为 Mate X5 典藏版', '小米 15',
                    '华为 Mate 40 Pro (4G版)', '小米 12S Pro', 'Redmi Note 9 (5G版)', '华为 Mate X2 (5G版)', 'iQOO 10',
                    '小米 15', '小米 15 Pro', '华为 Mate 70', '华为 Mate 70 Pro', '华为 Mate 70 Pro+', '华为 Mate 70 RS 非凡大师'
                ]
        elif category == "平板":
            return [
                'iPad 9代', 'iPad Air5', 'iPad mini6', 'iPad Pro 4代 11英寸', 'iPad 10代', 'iPad 6代', 'iPad 8代', 'iPad Air2', 'iPad mini5', 'iPad Air (M2) 11英寸',
                'iPad Air3', 'iPad mini4', 'iPad Air4', 'iPad 7代', 'iPad Pro 2代 11英寸', 'iPad 5代', 'iPad Pro 5代 12.9英寸', 'iPad Pro (M4) 11英寸', 'iPad mini7',
                'iPad Pro 3代 11英寸', 'iPad Pro 1代 11英寸'
            ]
        elif category == "智能手表":
            return [
                '苹果 Watch Series 9', '苹果 Watch Ultra 2',
                '华为 Watch GT4', '小米 Watch S3',
                '三星 Galaxy Watch 6'
            ]
        elif category == "耳机":
            return [
                '苹果 AirPods Pro 2', '苹果 AirPods 4',
                '华为 FreeBuds Pro 3', '小米 Buds 4',
                '索尼 WF-1000XM5'
            ]
        elif category == "笔记本":
            return [
                '苹果 MacBook Air', '苹果 MacBook Pro 14', '苹果 MacBook Pro 16',
                '华为 MateBook X Pro', '联想 ThinkPad X1',
                '戴尔 XPS 13', '惠普 Spectre x360'
            ]
        return []
    
    # 侧边栏 - 选择产品类别
    with st.sidebar:
        st.header("产品类别")
        category = st.selectbox(
            "选择产品类别",
            ["手机", "平板", "智能手表", "耳机", "笔记本"],
            key="category"
        )
        
        # 如果选择了手机类别，显示子类别选项
        subcategory = None
        if category == "手机":
            subcategory = st.radio(
                "手机品牌",
                ["苹果", "安卓"],
                key="phone_subcategory"
            )
    
    # 根据选择的类别和子类别加载产品名称
    product_names = load_product_names(category, subcategory)
    
    # Main content
    tabs = st.tabs(["BI价格策略(型号)"])
    
    with tabs[0]:
        st.header(f"BI价格策略({category}{' - ' + subcategory if subcategory else ''})")
        
        # 创建三列布局
        col1, col2, col3 = st.columns(3)
        
        # 第一列：选择产品型号（可多选）
        with col1:
            st.subheader("选择产品型号")
            
            # 全选选项
            select_all = st.checkbox("全选", key="select_all")
            
            # 根据全选状态预设默认值
            default_selections = product_names if select_all else []
            
            # 多选产品型号
            bi_products = st.multiselect(
                "产品型号（可多选）",
                product_names,
                default=default_selections,
                key="bi_products"
            )
            
            # 当用户手动更改选择时，更新全选状态
            if set(bi_products) == set(product_names) and not select_all:
                st.session_state.select_all = True
            elif set(bi_products) != set(product_names) and select_all:
                st.session_state.select_all = False
            
            # 当全选状态改变时，更新产品选择
            if select_all and set(bi_products) != set(product_names):
                st.session_state.bi_products = product_names
            elif not select_all and bi_products == product_names:
                st.session_state.bi_products = []
        
        # 第二列：选择日期
        with col2:
            st.subheader("选择日期")
            bi_date = st.date_input(
                "日期",
                current_date,
                max_value=current_date,
                key="bi_date"
            )
            
            # 检查是否是当天且时间小于15:10
            is_time_valid = True
            if bi_date == current_datetime.date() and (current_datetime.hour < 15 or (current_datetime.hour == 15 and current_datetime.minute < 10)):
                st.warning("⚠️ 当天数据需要等到15:10之后才能获取")
                is_time_valid = False
        
        # 第三列：设置预期竞争力
        with col3:
            st.subheader("设置预期竞争力")
            
            # 检查是否选择了多个产品
            if len(bi_products) > 1:
                # 创建一个通用的竞争力设置
                use_common_setting = st.checkbox("对所有产品使用相同的竞争力设置", value=True, key="use_common_setting")
                
                if use_common_setting:
                    # 使用单一竞争力设置
                    common_competitiveness = st.slider(
                        "所有产品的预期竞争力 (%)", 
                        min_value=0, 
                        max_value=100, 
                        value=50, 
                        step=1,
                        key="common_competitiveness"
                    ) / 100.0
                    
                    st.write(f"所有产品的预期竞争力: **{common_competitiveness:.1%}**")
                    
                    # 创建一个字典，为每个产品分配相同的竞争力值
                    bi_competitiveness_dict = {product: common_competitiveness for product in bi_products}
                else:
                    # 为每个产品单独设置竞争力
                    st.write("为每个产品单独设置竞争力:")
                    
                    # 创建一个字典来存储每个产品的竞争力设置
                    bi_competitiveness_dict = {}
                    
                    # 使用expander来节省空间
                    with st.expander("产品竞争力设置", expanded=True):
                        # 对于选择的每个产品，添加一个滑动条
                        for product in bi_products:
                            product_competitiveness = st.slider(
                                f"{product}", 
                                min_value=0, 
                                max_value=100, 
                                value=50, 
                                step=1,
                                key=f"competitiveness_{product}"
                            ) / 100.0
                            
                            bi_competitiveness_dict[product] = product_competitiveness
            else:
                # 单个产品的情况
                bi_competitiveness = st.slider(
                    "预期竞争力 (%)", 
                    min_value=0, 
                    max_value=100, 
                    value=50, 
                    step=1,
                    key="bi_competitiveness"
                ) / 100.0
                
                st.write(f"预期竞争力: **{bi_competitiveness:.1%}**")
                
                # 如果只有一个产品，也创建字典格式以保持一致
                if bi_products:
                    bi_competitiveness_dict = {bi_products[0]: bi_competitiveness}
                else:
                    bi_competitiveness_dict = {}
        
        # 运行按钮
        if st.button("运行", key="run_bi_strategy"):
            # 检查是否至少选择了一个产品
            if not bi_products:
                st.error("请至少选择一个产品型号")
            # 检查时间是否有效
            elif not is_time_valid:
                st.error("⚠️ 当天数据需要等到15:10之后才能获取，请选择其他日期或稍后再试")
            else:
                # 记录总处理开始时间
                total_start_time = time.time()
                
                # 创建一个进度条和计数显示（使用会话状态管理）
                if 'progress_value' not in st.session_state:
                    st.session_state.progress_value = 0
                
                progress_bar = st.progress(st.session_state.progress_value)
                progress_text = st.empty()
                progress_text.text(f"准备处理 {len(bi_products)} 个产品...")
                
                # 创建结果容器
                results_container = st.container()
                
                # 移除会话上下文获取相关代码
                
                # 获取基础数据（所有产品共用一次）
                with st.spinner("获取基础数据..."):
                    base_data = get_base_data_for_date(bi_date)
                    
                if base_data is None:
                    st.error("获取基础数据失败")
                else:
                    # 使用多线程并行处理多个产品
                    product_results = {}
                    
                    # 创建共享的结果字典和进度变量
                    results_lock = threading.Lock()
                    completed_count = 0
                    
                    # 处理单个产品的函数
                    def process_product(product, competitiveness):
                        nonlocal completed_count  # 在函数开始时声明nonlocal
                        try:
                            # 记录开始时间，用于计算处理时长
                            start_time = time.time()
                            
                            # 打印调试信息
                            print(f"开始处理产品: {product}, 竞争力: {competitiveness}")
                            
                            # 尝试运行BI价格策略计算
                            result = run_specific_product_bi_price(base_data, product, bi_date, competitiveness)
                            
                            # 计算处理时间
                            process_time = time.time() - start_time
                            result['process_time'] = process_time
                            
                            # 打印处理结果
                            print(f"产品处理完成: {product}, 结果: {result['success']}, 时间: {process_time:.2f}秒")
                            
                            # 更新共享状态
                            with results_lock:
                                product_results[product] = result
                                completed_count += 1  # 更新完成计数
                                # 更新进度信息到会话状态
                                current_progress = completed_count / len(bi_products)
                                st.session_state.progress_value = current_progress
                            
                            return result
                        except Exception as e:
                            # 捕获并记录异常详情
                            import traceback
                            error_details = traceback.format_exc()
                            print(f"处理产品 {product} 时出错: {str(e)}\n{error_details}")
                            
                            error_result = {
                                "success": False,
                                "message": f"处理出错: {str(e)}",
                                "error_details": error_details,
                                "data": None
                            }
                            
                            # 更新共享状态
                            with results_lock:
                                product_results[product] = error_result
                                completed_count += 1  # 更新完成计数
                                # 更新进度信息到会话状态
                                current_progress = completed_count / len(bi_products)
                                st.session_state.progress_value = current_progress
                            
                            return error_result
                    
                    # 使用单线程的定期刷新函数来更新进度条
                    def update_progress_bars():
                        """用于更新界面上的进度条，该函数在主线程中运行"""
                        while True:
                            # 使用会话状态中的值更新进度条
                            progress_bar.progress(st.session_state.progress_value)
                            progress_text.text(f"处理进度: {completed_count}/{len(bi_products)} 产品完成 ({st.session_state.progress_value:.0%})")
                            
                            # 判断是否所有处理已完成
                            if completed_count >= len(bi_products):
                                progress_bar.progress(1.0)
                                progress_text.text(f"所有 {len(bi_products)} 个产品处理完成!")
                                break
                                
                            # 睡眠一段时间再更新
                            time.sleep(0.1)
                    
                    # 使用线程池
                    try:
                        # 创建一个单独的线程来更新进度条
                        progress_updating_thread = threading.Thread(target=update_progress_bars)
                        progress_updating_thread.daemon = True
                        progress_updating_thread.start()
                        
                        # 创建线程池处理产品
                        with ThreadPoolExecutor(max_workers=min(4, len(bi_products))) as executor:
                            futures = {executor.submit(process_product, product, bi_competitiveness_dict[product]): product for product in bi_products}
                            
                            # 等待所有任务完成
                            for future in as_completed(futures):
                                product = futures[future]
                                try:
                                    future.result()
                                except Exception as e:
                                    import traceback
                                    error_details = traceback.format_exc()
                                    print(f"线程池执行 {product} 时异常: {str(e)}\n{error_details}")
                        
                        # 等待进度更新线程完成
                        progress_updating_thread.join(timeout=1.0)
                        
                        # 确保进度条显示完成
                        progress_bar.progress(1.0)
                        progress_text.text(f"所有 {len(bi_products)} 个产品处理完成!")
                        
                    except Exception as e:
                        st.error(f"处理过程中发生错误: {str(e)}")
                        
                    # 计算总处理时间
                    total_process_time = time.time() - total_start_time
                    
                    # 计算平均处理时间
                    avg_process_time = sum(result.get('process_time', 0) for result in product_results.values() if result) / len(product_results) if product_results else 0
                    
                    # 显示时间统计信息
                    st.success(f"所有产品处理完成！总耗时: {total_process_time:.2f}秒，平均每个产品耗时: {avg_process_time:.2f}秒")
                    
                    # 创建一个全局容器来显示所有结果
                    results_display = st.container()
                    
                    with results_display:
                        # 检查是否所有产品都处理失败
                        all_failed = all(not result.get('success', False) for result in product_results.values())
                        if all_failed:
                            st.error("所有产品处理均失败，请检查以下错误信息:")
                            
                            error_expander = st.expander("查看详细错误信息", expanded=True)
                            with error_expander:
                                for product, result in product_results.items():
                                    st.markdown(f"**产品: {product}**")
                                    st.error(result.get('message', '未知错误'))
                                    if 'error_details' in result and result['error_details']:
                                        st.code(result['error_details'], language="python")
                                    st.divider()
                        else:
                            # 处理成功的产品
                            successful_products = [product for product, result in product_results.items() if result.get('success', False)]
                            
                            if successful_products:
                                # 汇总多产品结果
                                if len(successful_products) > 1:
                                    # 汇总所有结果数据
                                    all_chosen_df = []
                                    all_sku_level_df = []
                                    all_selected_results = []
                                    
                                    for product in successful_products:
                                        result = product_results[product]
                                        if result['success']:
                                            # 添加产品标识到数据框
                                            if result.get('data') is not None and not result.get('data').empty:
                                                df_copy = result['data'].copy()
                                                df_copy['product_name_source'] = product
                                                all_chosen_df.append(df_copy)
                                            
                                            if result.get('sku_level_df') is not None and not result.get('sku_level_df').empty:
                                                df_copy = result['sku_level_df'].copy()
                                                if 'product_name' not in df_copy.columns:
                                                    df_copy['product_name_source'] = product
                                                all_sku_level_df.append(df_copy)
                                            
                                            if result.get('selected_chosen_results') is not None and not result.get('selected_chosen_results').empty:
                                                df_copy = result['selected_chosen_results'].copy()
                                                if 'product_name' not in df_copy.columns:
                                                    df_copy['product_name_source'] = product
                                                all_selected_results.append(df_copy)
                                    
                                    # 合并数据框
                                    combined_chosen_df = pd.concat(all_chosen_df, ignore_index=True) if all_chosen_df else None
                                    combined_sku_level_df = pd.concat(all_sku_level_df, ignore_index=True) if all_sku_level_df else None
                                    combined_selected_results = pd.concat(all_selected_results, ignore_index=True) if all_selected_results else None
                                    
                                    # 展示汇总结果
                                    st.header("汇总结果")
                                    
                                    # 汇总统计信息
                                    col1, col2, col3 = st.columns(3)
                                    
                                    with col1:
                                        st.metric("处理产品数", len(successful_products))
                                    
                                    with col2:
                                        if combined_selected_results is not None:
                                            total_sku = len(combined_selected_results)
                                            st.metric("总数据量", total_sku)
                                    
                                    with col3:
                                        if combined_chosen_df is not None:
                                            avg_competitiveness = combined_chosen_df['target'].astype(str).str.rstrip('%').astype(float).mean() / 100
                                            st.metric("平均目标竞争力", f"{avg_competitiveness:.1%}")
                                    
                                    # 创建选项卡，便于查看不同级别的数据
                                    data_tabs = st.tabs(["策略数据摘要", "SKU-LEVEL级别数据", "PPV级别数据"])
                                    
                                    # 创建产品筛选器（全局共享）
                                    product_filter = st.multiselect(
                                        "按产品筛选", 
                                        options=successful_products,
                                        default=successful_products,
                                        key="product_filter"
                                    )
                                    
                                    # 保存数据到session state，确保在下载时数据不会丢失
                                    st.session_state['processed_data'] = {
                                        'combined_selected_results': combined_selected_results,
                                        'combined_sku_level_df': combined_sku_level_df,
                                        'combined_chosen_df': combined_chosen_df,
                                        'successful_products': successful_products,
                                        'product_filter': product_filter,
                                        'bi_date': bi_date,
                                        'product_results': product_results,
                                        'bi_products': bi_products,
                                        'bi_competitiveness_dict': bi_competitiveness_dict
                                    }
                                    
                                    # 策略数据摘要选项卡 (放在第一个位置)
                                    with data_tabs[0]:
                                        if combined_chosen_df is not None:
                                            # 筛选数据
                                            if product_filter:
                                                if 'product_name' in combined_chosen_df.columns:
                                                    filtered_chosen_df = combined_chosen_df[combined_chosen_df['product_name'].isin(product_filter)]
                                                else:
                                                    filtered_chosen_df = combined_chosen_df[combined_chosen_df['product_name_source'].isin(product_filter)]
                                                
                                                # 显示筛选后的详细结果
                                                st.dataframe(filtered_chosen_df)
                                                
                                                # 提供下载按钮
                                                if filtered_chosen_df is not None and not filtered_chosen_df.empty:
                                                    csv_data = filtered_chosen_df.to_csv(index=False).encode('utf-8')
                                                    st.download_button(
                                                        label="下载策略数据摘要",
                                                        data=csv_data,
                                                        file_name=f"chosen_df_summary_{bi_date}.csv",
                                                        mime="text/csv",
                                                        key=f"previous_download_summary_data_{session_id}"  # 添加会话ID以确保唯一性
                                                    )
                                                else:
                                                    st.write("策略数据摘要不可用")
                                    
                                    # SKU-LEVEL级别数据选项卡 (放在第二个位置)
                                    with data_tabs[1]:
                                        if combined_sku_level_df is not None:
                                            # 筛选数据
                                            if product_filter:
                                                if 'product_name' in combined_sku_level_df.columns:
                                                    filtered_sku_level_df = combined_sku_level_df[combined_sku_level_df['product_name'].isin(product_filter)]
                                                else:
                                                    filtered_sku_level_df = combined_sku_level_df[combined_sku_level_df['product_name_source'].isin(product_filter)]
                                                
                                                # 显示筛选后的详细结果
                                                st.dataframe(filtered_sku_level_df)
                                                
                                                # 提供下载按钮
                                                if filtered_sku_level_df is not None and not filtered_sku_level_df.empty:
                                                    csv_data = filtered_sku_level_df.to_csv(index=False).encode('utf-8')
                                                    st.download_button(
                                                        label="下载SKU-LEVEL级别数据",
                                                        data=csv_data,
                                                        file_name=f"sku_level_data_{bi_date}.csv",
                                                        mime="text/csv",
                                                        key=f"previous_download_sku_data_{session_id}"  # 添加会话ID以确保唯一性
                                                    )
                                                else:
                                                    st.write("SKU-LEVEL级别数据不可用")
                                    
                                    # PPV级别数据选项卡 (放在第三个位置)
                                    with data_tabs[2]:
                                        if combined_selected_results is not None:
                                            # 筛选数据
                                            if product_filter:
                                                if 'product_name' in combined_selected_results.columns:
                                                    filtered_results = combined_selected_results[combined_selected_results['product_name'].isin(product_filter)]
                                                else:
                                                    filtered_results = combined_selected_results[combined_selected_results['product_name_source'].isin(product_filter)]
                                                
                                                # 显示筛选后的详细结果
                                                st.dataframe(filtered_results)
                                                
                                                # 提供下载按钮
                                                if filtered_results is not None and not filtered_results.empty:
                                                    csv_data = filtered_results.to_csv(index=False).encode('utf-8')
                                                    st.download_button(
                                                        label="下载PPV级别数据",
                                                        data=csv_data,
                                                        file_name=f"ppv_level_data_{bi_date}.csv",
                                                        mime="text/csv",
                                                        key=f"previous_download_ppv_data_{session_id}"  # 添加会话ID以确保唯一性
                                                    )
                                                else:
                                                    st.write("PPV级别数据不可用")
                                
                                # 处理完成后，展示所有结果 (保留单个产品的详细结果展示)
                                with results_container:
                                    for i, product in enumerate(bi_products):
                                        result = product_results.get(product)
                                        if result:
                                            # 使用expander包装每个产品的结果，默认折叠以节省空间
                                            with st.expander(f"产品 {i+1}/{len(bi_products)}: {product}", expanded=False):
                                                if result['success']:
                                                    process_time = result.get('process_time', 0)
                                                    st.success(f"{result['message']} (耗时: {process_time:.2f}秒)")
                                                    
                                                    # 使用修改后函数返回的chosen_df数据
                                                    chosen_df = result['data']
                                                    sku_level_df = result.get('sku_level_df')
                                                    selected_chosen_results = result.get('selected_chosen_results')
                                                    
                                                    if chosen_df is not None and not chosen_df.empty:
                                                        # 展示汇总数据
                                                        st.subheader("价格策略结果摘要")
                                                        
                                                        # 计算关键指标
                                                        total_records = len(chosen_df)
                                                        
                                                        # 创建关键指标卡片
                                                        col1, col2, col3 = st.columns(3)
                                                        
                                                        with col1:
                                                            st.metric("SKU总数", total_records)
                                                        
                                                        with col2:
                                                            # 计算平均竞争力（如果列存在）
                                                            if 'real_compete_rate' in chosen_df.columns:
                                                                avg_compete_rate = chosen_df['real_compete_rate'].mean()
                                                                st.metric("平均竞争力", f"{avg_compete_rate:.1%}")
                                                            else:
                                                                st.metric("目标竞争力", f"{bi_competitiveness_dict[product]:.1%}")
                                                        
                                                        with col3:
                                                            # 计算价格变化百分比（如果存在原价和新价格列）
                                                            if 'finalprice' in chosen_df.columns and 'chosen_price' in chosen_df.columns:
                                                                avg_price_change = ((chosen_df['chosen_price'] - chosen_df['finalprice']) / chosen_df['finalprice']).mean()
                                                                st.metric("平均价格变化", f"{avg_price_change:+.1%}")
                                                        
                                                        # 展示价格调整图表
                                                        st.subheader("各级别价格调整")
                                                        
                                                        # 检查必要的列是否存在
                                                        required_cols = ['product_level_name', 'finalprice', 'chosen_price']
                                                        if all(col in chosen_df.columns for col in required_cols):
                                                            # 按产品等级分组
                                                            level_groups = chosen_df.groupby('product_level_name')
                                                            
                                                            # 准备绘图数据
                                                            levels = []
                                                            old_prices = []
                                                            new_prices = []
                                                            
                                                            for level, group in level_groups:
                                                                levels.append(level)
                                                                old_prices.append(group['finalprice'].mean())
                                                                new_prices.append(group['chosen_price'].mean())
                                                            
                                                            # 创建价格对比图
                                                            fig, ax = plt.subplots(figsize=(10, 6))
                                                            x = np.arange(len(levels))
                                                            width = 0.35
                                                            
                                                            # 绘制条形图
                                                            ax.bar(x - width/2, old_prices, width, label='原价格')
                                                            ax.bar(x + width/2, new_prices, width, label='策略价格')
                                                            
                                                            # 设置图表标签
                                                            ax.set_ylabel('价格 (¥)')
                                                            ax.set_title(f'{product} 各等级价格调整')
                                                            ax.set_xticks(x)
                                                            ax.set_xticklabels(levels)
                                                            ax.legend()
                                                            
                                                            # 价格标签
                                                            for i, v in enumerate(old_prices):
                                                                ax.text(i - width/2, v + 50, f'¥{v:,.0f}', ha='center', va='bottom')
                                                            
                                                            for i, v in enumerate(new_prices):
                                                                ax.text(i + width/2, v + 50, f'¥{v:,.0f}', ha='center', va='bottom')
                                                            
                                                            st.pyplot(fig)
                                                        
                                                        # 展示详细表格数据
                                                        st.subheader("价格策略详细数据")
                                                        
                                                        # 展示核心列，根据数据框中可用的列进行选择
                                                        display_columns = []
                                                        column_map = {}
                                                        
                                                        # 添加核心列（如果存在）
                                                        core_columns = {
                                                            'product_level_name': '产品等级',
                                                            'product_sku_name': 'SKU名称',
                                                            'finalprice': '原价格',
                                                            'ahs_couponed_price': '原券后价',
                                                            'chosen_price': '策略价格',
                                                            'chosen_price_couponed': '策略券后价',
                                                            'chosen_type': '策略类型',
                                                            'bi_price': 'BI价格',
                                                            'smooth_price': '平滑价格',
                                                            'anti1_price': '竞争价格1',
                                                            'anti2_price': '竞争价格2',
                                                            'reanti_price': '再竞争价格',
                                                            'bi_tag': 'BI标签',
                                                            'smooth_tag': '平滑标签',
                                                            'deanti_tag': '去竞争标签'
                                                        }
                                                        
                                                        for col, display_name in core_columns.items():
                                                            if col in chosen_df.columns:
                                                                display_columns.append(col)
                                                                column_map[col] = display_name
                                                        
                                                        # 如果找到了要显示的列
                                                        if display_columns:
                                                            # 创建要显示的数据框
                                                            display_df = chosen_df[display_columns].copy()
                                                            
                                                            # 重命名列
                                                            display_df = display_df.rename(columns=column_map)
                                                            
                                                            # 显示数据框
                                                            st.dataframe(display_df)
                                                        else:
                                                            # 如果没有找到特定列，显示原始数据框
                                                            st.dataframe(chosen_df)
                                                        
                                                        # 提供下载按钮部分
                                                        st.subheader("数据下载")
                                                        
                                                        col1, col2, col3 = st.columns(3)
                                                        
                                                        with col1:
                                                            # 下载chosen_df数据
                                                            st.download_button(
                                                                label="下载策略数据摘要",
                                                                data=chosen_df.to_csv(index=False).encode('utf-8'),
                                                                file_name=f"bi_price_strategy_{product}_{bi_date}.csv",
                                                                mime="text/csv",
                                                                key=f"download_summary_{product}"  # 添加唯一的key
                                                            )
                                                        
                                                        with col2:
                                                            # 下载sku_level_df数据（如果存在）
                                                            if sku_level_df is not None and not isinstance(sku_level_df, bool) and not sku_level_df.empty:
                                                                st.download_button(
                                                                    label="下载SKU-LEVEL级别数据",
                                                                    data=sku_level_df.to_csv(index=False).encode('utf-8'),
                                                                    file_name=f"sku_level_{product}_{bi_date}.csv",
                                                                    mime="text/csv",
                                                                    key=f"download_sku_{product}"  # 添加唯一的key
                                                                )
                                                            else:
                                                                st.write("SKU级别数据不可用")
                                                        
                                                        with col3:
                                                            # 下载selected_chosen_results数据（如果存在）
                                                            if selected_chosen_results is not None and not isinstance(selected_chosen_results, bool) and not selected_chosen_results.empty:
                                                                st.download_button(
                                                                    label="下载PPV级别数据",
                                                                    data=selected_chosen_results.to_csv(index=False).encode('utf-8'),
                                                                    file_name=f"selected_results_{product}_{bi_date}.csv",
                                                                    mime="text/csv",
                                                                    key=f"download_ppv_{product}"  # 添加唯一的key
                                                                )
                                                            else:
                                                                st.write("选定结果数据不可用")
                                                else:
                                                    st.warning("返回的数据为空，请尝试其他参数组合。")
                                                st.divider()
                                    else:
                                        st.error(result['message'])
                                        # 显示更详细的错误信息
                                        if 'error_details' in result and result['error_details']:
                                            with st.expander("查看详细错误堆栈"):
                                                st.code(result['error_details'], language="python")
                                        
                                        # 尝试提供错误原因分析
                                        error_msg = result.get('message', '').lower()
                                        if "no such column" in error_msg:
                                            st.info("可能原因: 查询的列名不存在，请检查SQL语句或数据表结构")
                                        elif "key error" in error_msg:
                                            st.info("可能原因: 尝试访问不存在的数据键或索引")
                                        elif "timeout" in error_msg or "connection" in error_msg:
                                            st.info("可能原因: 数据库连接问题，可能是网络问题或服务器响应超时")
                                        
                                        # 添加问题排查建议
                                        st.info("建议: 尝试单独运行此产品，以获取更详细的错误信息")

if __name__ == "__main__":
    
    main()

    # 启动： nohup streamlit run app.py --server.port 8501 --server.address 0.0.0.0 &